package com.ilas.sql;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.ilas.entity.Tenant;
import lombok.Synchronized;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;

import javax.sql.DataSource;

/**
 * <p>创建并连接数据库</p>
 *
 * @Author Xbf
 * @Date 2021/3/25 17:07
 */
public class ScriptSql {
    /**
     * 创建并连接数据库，注意加载数据库jar连接jar
     *
     * @return
     */
    public void getConn() {
        String mysqlDriver = "com.mysql.cj.jdbc.Driver";
        String newUrl = "jdbc:mysql://localhost:3306/cultural_cloud?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "root";
        String db = "sql_db";
        Connection conn = null;
        Statement newSmt = null;
        try {
            Class.forName(mysqlDriver);
            conn = DriverManager.getConnection(newUrl, username, password);
            if (conn != null) {
                newSmt = conn.createStatement();
                //创建数据库
                int i = newSmt.executeUpdate("CREATE DATABASE " + db + ";");
                System.out.println(i);
                // DDL语句返回值为0;创建数据库
                if (i > 0) {
                    System.out.println("创建成功！");
                    //打开 转向数据库
                    newSmt.execute("USE " + db + ";");
                    //执行脚本
                    exeSQLSript(db);
                } else {
                    System.out.println("创建失败！");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (newSmt != null) {
                try {
                    newSmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void exeSQLSript(String db) throws Exception{
        //读取sql脚本文件保存到数组中
        String[] sqlName = {"D:\\20210325\\cultural_cloud.20210105.sql"};
        //循环执行sql脚本
        for (int i = 0; i < sqlName.length; i++) {
            //利用 Ant 的SQL Task来实现执行SQL 脚本的功能。ant 包中的 SQLExec类的扩展，此时需要将ant 包(ant.jar)导入
            SQLExec sqlExec = new SQLExec();
            String mysqlDriver = "com.mysql.cj.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/" + db + "?characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            // 设置数据库参数
            sqlExec.setDriver(mysqlDriver);
            sqlExec.setUrl(url);
            sqlExec.setUserid(username);
            sqlExec.setPassword(password);
            //防止乱码，sql文件也需要设置为utf-8
            sqlExec.setEncoding("UTF8");
            //设置sql脚本
            sqlExec.setSrc(new File(sqlName[i]));
            // 设置是否输出 true-打印,false-不打印
            sqlExec.setPrint(false);
            // 要指定这个属性，不然会出错
            sqlExec.setProject(new Project());
            sqlExec.setOutputEncoding("UTF8");
            sqlExec.setAutocommit(true);
            sqlExec.execute();

        }
    }

    @Synchronized
    public static DataSource create(Tenant tenant){
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        String mysqlDriver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/" + tenant.getTenantName() + "?characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "root";
        dataSource.setDriverClassName(mysqlDriver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }

    public static void main(String[] args) {
        ScriptSql sql = new ScriptSql();
        sql.getConn();
    }
}
